#!/usr/bin/env python
# -*- encoding:utf-8 -*-
'''
@Author: 陈亮
@LastEditors: FuXueshuang
@GitHub: https://github.com/drinkwd
@Description: 事件管理
@Date: 2019-02-28 14:18:52
@LastEditTime: 2019-05-08 16:28:16
'''


import json

from flask import Blueprint, g, make_response, send_file
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib import auth
from lib.table import table
from lib.validform import V, ValidateForm

app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 事件管理统计区
@app.route('/api/event/statistics', methods=['GET'])
@auth.permission('event', 'readOnly')
def statistics():
    data = {
        "status": "success",
        "dataSource": {
            "level": {
                "level1": 20,
                "level2": 30,
                "level3": 2,
                "level4": 0
            },
            "total": 0,
            "status": {
                "process": 10,
                "completed": 20,
                "undone": 14
            },
            "waves": [
            ],
        }
    }
    # return json.dumps(data)
    try:
        # 威胁级别统计
        g.cursor.execute("""
        select  coalesce(sum(case when threaten_level = 1 then 1 else 0 end),0) as "level1",
                coalesce(sum(case when threaten_level = 2 then 1 else 0 end),0) as "level2",
                coalesce(sum(case when threaten_level = 3 then 1 else 0 end),0) as "level3",
                coalesce(sum(case when threaten_level = 4 then 1 else 0 end),0) as "level4"
        from h_attack_eventinfo
        """)
        data["dataSource"]["level"] = g.cursor.fetchone()
        # 处置状态统计
        g.cursor.execute("""
        select  coalesce(sum(case when handle_status = 'fixing' then 1 else 0 end),0) as "process",
                coalesce(sum(case when handle_status = 'fix' then 1 else 0 end),0) as "completed",
                coalesce(sum(case when handle_status = 'unfix' then 1 else 0 end),0) as "undone",
                coalesce(sum(case when handle_status = 'defense' then 1 else 0 end),0) as "defense"
        from h_attack_eventinfo
        """)
        data["dataSource"]["status"] = g.cursor.fetchone()
        # 上报数量统计
        g.cursor.execute("""
            select COALESCE (event_num, 0) as event_count,r_time
            from (
                WITH RECURSIVE t(r_time) AS (
                    SELECT date_trunc('hour',CURRENT_TIMESTAMP-interval '1 day')
                UNION ALL
                    SELECT r_time-interval '1 day' FROM t
                )
                SELECT substr(d2t(r_time),1,10) as r_time
                FROM t
                LIMIT 15
                )a
                left join (
                select substr(d2t(static_date),1,10) as static_date,event_num from h_event_data
                ) b
                on a.r_time = b.static_date
                order by r_time
        """)
        data["dataSource"]["waves"] = g.cursor.fetchall()
        g.cursor.execute("""
            select  count(1) as total from h_attack_eventinfo
        """)
        data["dataSource"]["total"] = g.cursor.fetchone()['total']
    except Exception, e:
        return json.dumps({"status": "except", 'msg': str(e)})
    return json.dumps(data)


# 事件管理表格信息
@app.route('/api/event/eventtable', methods=['POST'])
@auth.permission('event', 'readOnly')
def eventtable():
    try:
        data = {
            "total": 0,
            "dataSource": []
        }
        tb = table()
        g.cursor.execute("""
        with n as ( select a.event_id,a.event_name,a.victim_unit,a.unit,a.start_time,a.new_time,a.info_sys_name,a.risk_level,a.sys_node_name,a.handle_status
                    from (select a.event_id as event_id,a.event_name as event_name,unit, c.victim_unit as victim_unit,d2t(start_time) as start_time,d2t(new_time) as new_time,d.info_sys_name as info_sys_name, coalesce(string_agg(e.sys_node_name,','),'-') as sys_node_name,handle_status,
                    case when threaten_level = 1 then  '低 危'
                        when threaten_level = 2 then  '中 危'
                        when threaten_level = 3 then  '高 危'
                        when threaten_level = 4 then  '严 重' end as risk_level
                    from h_attack_eventinfo as a
                    left join h_event_victimperson as b
                    on a.event_id = b.event_id
                    left join h_victim_info as c
                    on b.assert_id = c.assert_id and b.sys_id = c.sys_id
                    left join h_information_system as d
                    on c.sys_id = d.info_sys_id
                    left join h_system_node as e
                    on d.info_sys_id = e.info_sys_id
                    group by a.event_id,
                    event_name ,unit,
                    victim_unit,
                    start_time,
                    new_time,
                    info_sys_name,
                    handle_status) as a  %s) select count(1) as total from n
        """ % (tb.where(['a.event_name', 'a.victim_unit', 'a.info_sys_name', 'a.sys_node_name'])))
        data['total'] = g.cursor.fetchone()['total']
        g.cursor.execute("""
            select a.event_id,a.event_name,a.victim_unit,a.unit,a.start_time,a.new_time,a.info_sys_name,a.risk_level,a.sys_node_name,a.handle_status
            from (select a.event_id as event_id,a.event_name as event_name,unit, c.victim_unit as victim_unit,d2t(start_time) as start_time,d2t(new_time) as new_time,d.info_sys_name as info_sys_name, coalesce(string_agg(e.sys_node_name,','),'-') as sys_node_name,handle_status,
            case when threaten_level = 1 then  '低 危'
                when threaten_level = 2 then  '中 危'
                when threaten_level = 3 then  '高 危'
                when threaten_level = 4 then  '严 重' end as risk_level
            from h_attack_eventinfo as a
            left join h_event_victimperson as b
            on a.event_id = b.event_id
            left join h_victim_info as c
            on b.assert_id = c.assert_id and b.sys_id = c.sys_id
            left join h_information_system as d
            on c.sys_id = d.info_sys_id
            left join h_system_node as e
            on d.info_sys_id = e.info_sys_id
            group by a.event_id,
            event_name ,
            unit,
            victim_unit,
            start_time,
            new_time ,
            info_sys_name,
            handle_status) as a  %s %s %s %s
        """ % (tb.where(['a.event_name', 'a.victim_unit', 'a.info_sys_name', 'a.sys_node_name']), tb.orderBy(), tb.offset(), tb.limit()))
        data['dataSource'] = g.cursor.fetchall()
        return json.dumps({"status": "success", "data": data})
    except Exception, e:
        return json.dumps({"status": "except", 'msg': e})


# 事件管理详情信息
@app.route('/api/event/eventDetail', methods=['POST'])
@auth.permission('event', 'readOnly')
def eventDetail():
    data = {
        "summary": {},
        "threatLoad": [],
        "attackinfo": {},
        "victim_info": {},
        "beacon": [],
        "ruleinfo": [],
        "timeline": []
    }
    try:
        form = ValidateForm(
            eventId=['事件id', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 规则匹配
        g.cursor.execute("""
            select auto_analysis_result,case when d.time_span_value is not null then '时间跨度'||d.time_span_value||d.time_span_unit||'、'
                                            else '' end||
                                        case when d.risk_level is not null and d.risk_level_condition='>' then '风险等级大于'||d.risk_level||'级、'
                                            when d.risk_level is not null and d.risk_level_condition='=' then '风险等级等于'||d.risk_level||'级、'
                                            when d.risk_level is not null and d.risk_level_condition='<' then '风险等级小于'||d.risk_level||'级、'
                                        else '' end||
                                        case when d.repeated_times is not null and d.repeated_condition='>' then '出现次数大于'||d.repeated_times
                                            when d.repeated_times is not null and d.repeated_condition='=' then '出现次数等于'||d.repeated_times
                                            when d.repeated_times is not null and d.repeated_condition='<' then '出现次数小于'||d.repeated_times
                                        else '' end||
                                        case when d.key_word is not null then d.key_word_type||'为'||d.key_word||'，'
                                        else '' end as ruleinfo from h_attack_beacon as a
            inner join h_auto_analysis_result as b
            on a.beacon_id = b.beacon_id
            inner join h_auto_analysis_rule as d on d.rule_id = b.rule_id
            where d.state='1' and event_id = %(eventId)s
            group by auto_analysis_result,ruleinfo
        """, data)
        data["ruleinfo"] = g.cursor.fetchall()
        # 攻击摘要信息
        g.cursor.execute("""
            select event_name, attack_num,d2t(start_time) as start_time,d2t(new_time) as new_time,d2t(ts_time) as ts_time,unit,version,handle_status,handle_desc,event_desc,
            case 	when threaten_level=1 then '低危'
                    when threaten_level=2 then '中危'
                    when threaten_level=3 then '高危'
                    when threaten_level=4 then '严重' end as threaten_level,
            case 	when handle_status='fix' then '已修复'
                    when handle_status='unfix' then '未修复'
                    when handle_status='fixing' then '处修复'
                    when handle_status='defense' then '成功防御' end as handle_status
            from h_attack_eventinfo where event_id = %(eventId)s
        """, data)
        data["summary"] = g.cursor.fetchone()
        # 威胁载荷
        g.cursor.execute("""
            select distinct fileinfo_name,fileinfo_type,risk,malname,maltype,vector_cve,vector_behavior,md5
            from h_threat_load where event_id = %(eventId)s
        """, data)
        data["threatLoad"] = g.cursor.fetchall()
        # 攻击者信息
        g.cursor.execute("""
            select a.attack_ip,attack_country,attack_area,attack_city,attack_organ,attack_sign
            from h_attack_person as a
            left join h_event_attackperson as b
            on a.attack_ip = b.attack_ip where event_id = %(eventId)s
        """, data)
        data["attackinfo"] = g.cursor.fetchall()
        # 受害信息
        g.cursor.execute("""
            select c.info_sys_id, description,victim_unit,victim_country,victim_area,victim_city,info_sys_name,coalesce(string_agg(sys_node_name,','),'-') as sys_node_name
            from h_victim_info as a
            left join h_event_victimperson as b
            on a.assert_id = b.assert_id
            left join h_information_system as c
            on a.sys_id = c.info_sys_id
            left join h_system_node as d
            on c.info_sys_id = d.info_sys_id
            where event_id = %(eventId)s
            group by c.info_sys_id,description,victim_unit,victim_country,victim_area,victim_city,info_sys_name
        """, data)
        data["victim_info"] = g.cursor.fetchone()
        # 信标信息
        g.cursor.execute("""
            select ip,domain,email,url,case when threat_type = 'c2' then 'C2服务器'
                    when threat_type = 'fm' then '放马服务器'
                    when threat_type = 'gm' then '挂马服务器'
                    when threat_type = 'other' then '其他' end as threat_type
            from (select  string_agg(case when sign_type = 'ip' then sign_value else null end,',' )  as ip,
            string_agg (case when sign_type = 'domain' then sign_value else null end ,',')as domain,
            string_agg (case when sign_type = 'email' then sign_value else null end ,',')as email,
            string_agg (case when sign_type = 'url' then sign_value else null end ,',')as url,threat_type
            from h_attack_beacon where event_id = %(eventId)s group by threat_type) as a
        """, data)
        data["beacon"] = g.cursor.fetchall()
        # 分析流水
        g.cursor.execute("""
            select a.event_id,a.operate  as  analysis_source,
                                    a.analysis_state,
                                    d2t(a.r_time) as r_time
                                    ,a.risk_event_name,
                                    case when analysis_result = '0' then '未生成风险事项'
                                         when analysis_result = '1' then '生成风险事项'
                                    end as analysis_result,
                                    b.person_name as r_person,
                                    c.risk_event_id,
                                    string_agg(distinct d.rule_item, ',') as match_rule,a.analysis_id
            from h_analysis_event_record a
            left join sys_person b
            on a.r_person=b.person_id
            left join h_risk_event_info c
            on a.analysis_id = c.analysis_id
            left join h_auto_analysis_rule d
            on a.match_rule::integer=d.rule_id
            where source !='2' and event_id = %(eventId)s
            group by event_id,operate,analysis_state,a.r_time,a.risk_event_name,b.person_name,a.analysis_id,analysis_result,c.risk_event_id
            order by  a.r_time desc
        """, data)
        data["timeline"] = g.cursor.fetchall()
        return json.dumps({"data": data, "status": "success"})
    except Exception, e:
        return json.dumps({"data": str(e), "status": "fail"})


# 分析报告信息
@app.route('/api/event/analyzeReport', methods=['POST'])
@auth.permission('event', 'readOnly')
def analyzeReport():
    try:
        data = {
        }
        form = ValidateForm(
            eventId=['事件id', V.required()],
            analysisId=['分析id', V.required()],
            limit=['限制条数', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 分析结论+标题
        g.cursor.execute("""
            select analysis_title,d2t(b.r_time) as analysis_end_time,c.person_name as r_person,d2t(b.r_time) as r_time,
                    case when b.remark is null or b.remark=''  then '-' else b.remark end as  remark,
                    case when b.solution is null or b.solution=''  then '-' else b.solution end as  solution,
                    case when is_risk_event='0' then '否' when is_risk_event='1' then '是' end as is_risk_event,
                    case when analysis_basis is null or analysis_basis= '' then '-' else analysis_basis end as analysis_basis
            from h_analysis_info a
            left join h_analysis_conclusion b
            on  a.analysis_id=b.analysis_id
            left join sys_person c
            on c.person_id = b.r_person
            where event_id= %(eventId)s and a.analysis_id = %(analysisId)s
        """, data)
        data["conclusion"] = g.cursor.fetchone()
        # 风险事项基本信息
        g.cursor.execute("""
        select risk_event_name,risk_event_type,c.person_name as r_person,d2t(a.r_time) as r_time,
                case    when risk_event_content = '' or risk_event_content = null then '-' else risk_event_content end as risk_event_content ,
                case    when risk_event_level='1' then '一般'
                        when risk_event_level='2' then '较大'
                        when risk_event_level='3' then '重大'
                        when risk_event_level='4' then '特大' end as risk_event_level
        from h_analysis_conclusion a
        left join h_analysis_info b
        on a.analysis_id = b.analysis_id
        left join sys_person c
        on a.r_person = c.person_id
        where event_id =%(eventId)s and a.analysis_id = %(analysisId)s
        """, data)
        data["basicinfo"] = g.cursor.fetchone()
        # 关键要素
        g.cursor.execute("""
            select  element_name
            from h_analysis_element_info a
            left join h_analysis_info b
            on a.analysis_id = b.analysis_id
            where event_id =%(eventId)s and b.analysis_id = %(analysisId)s and state = '1'
        """, data)
        data["element"] = g.cursor.fetchall()
        # 影响范围
        g.cursor.execute("""
             select
                                case when string_agg(distinct e.sys_node_name,',') is not null then string_agg(distinct e.sys_node_name,',') else '-' end as effect_sys_node,
                                case when string_agg(distinct f.info_sys_name,',') is not null then string_agg(distinct f.info_sys_name,',') else '-' end as effect_info_sys,
                                case when string_agg(distinct g.agency_name,',') is not null then string_agg(distinct g.agency_name,',') else '-' end as effect_agency
                            from h_analysis_element_info as a
                            left join h_element_detail as b
                            on a.element_id = b.element_id
                            left join h_system_node as e
                            on b.sys_id = e.sys_node_id
                            left join h_information_system as f
                            on f.info_sys_id = e.info_sys_id
                            left join sys_agency_info as g
                            on g.agency_id = f.agency_id
                            where a.analysis_id = %(analysisId)s
            """, data)
        data["effect"] = g.cursor.fetchone()
        # 相关风险事项
        g.cursor.execute("""
            select risk_event_name,risk_event_level,risk_event_type,risk_event_content
            from h_risk_event_info a
            inner join (
                select related_id::int from h_analysis_info a
                left join h_related_analysis_info b
                on a.analysis_id = b.analysis_id
                where b.related_title_type='2' and event_id =%(eventId)s and a.analysis_id = %(analysisId)s
            ) as c on a.risk_event_id = c.related_id
        """, data)
        data["related"] = g.cursor.fetchall()
        # 附录信息
        g.cursor.execute("""
            select a.element_id, a.log_id, a.log_type, d2t(a.ts) as ts, a.sys_id, a.sys_name,
                    case when a.log_level = '1'then '低 危'
                         when a.log_level = '2'then '中 危'
                         when a.log_level = '3'then '高 危'
                         when a.log_level = '4'then '严 重' end as log_level,
                                    a.unit_id, a.raw_msg, a.sip, a.sport, a.sip_city, a.sip_country, a.dip, a.dport,
                                    a.dip_city, a.dip_country, a.proto, a.response_action, a.url, a.tag, a.event_desc,
                                    a.md5s, a.behavior_name_level, a.malname, a.virus_type, a.virus_family, a.
                                    virus_behavior, a.control_content, a.dev_name, a.dev_id, a.action, a.cve_id,
                                    a.vul_name, a.vul_type, a.client_ip, a.operate_user, a.result, a.action_state,
                                    a.flow_file_type, a.flow_data_detail, a.final_result, a.is_malicious,
                                    a.file_type, a.network_monitor, a.disposal_state
                                from h_element_detail a
                                left join h_analysis_element_info b
                                on a.element_id=b.element_id
                                where b.analysis_id= %(analysisId)s limit %(limit)s
        """, data)
        data["appendix"] = g.cursor.fetchall()
        return json.dumps({"data": data, "status": "success"})
    except Exception, e:
        return json.dumps({"data": str(e), "status": "fail"})


@app.route('/api/event/download', methods=['POST'])
@auth.permission('event')
def download_template():
    try:
        form = ValidateForm(
            path=['原始日志', V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        response = make_response(send_file(data['path'].encode('utf8')))
        response.headers["Content-Disposition"] = "attachment; filename={}".format(data['path'].split('/')[-1])
        return response
    except Exception, e:
        return json.dumps({"status": "fail", "msg": str(e)})
